--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_ctr_remise AS (SELECT * FROM &erp.ctr_remise WHERE 1=2);

/*
select * from (
SELECT &outils.stragg(source) source,remdat_id, classe_cod, classeval_cod, seuil_qtmt, rembase_cod, combadv_id, promo_cod, refe_cod, refe_ind, enreg_id, classeval_lib, remise_typ, remise_tx, remise_mt, sanscomm_typ, fin_dat, poidsvrt
FROM (
  SELECT 'uv' source ,g.* FROM se_ctr_remise g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ctr_remise g
  )
GROUP BY  remdat_id, classe_cod, classeval_cod, seuil_qtmt, rembase_cod, combadv_id, promo_cod, refe_cod, refe_ind, enreg_id, classeval_lib, remise_typ, remise_tx, remise_mt, sanscomm_typ, fin_dat, poidsvrt
HAVING Count(1)!=2) r
ORDER BY remdat_id*/

DELETE FROM se_ctr_remise;

INSERT INTO se_ctr_remise
  (SELECT  (select remdat_id FROM &erp.ctr_remdat f
           WHERE f.remcod_cod='RLA' AND f.DEBUT_DAT='01/01/2015'  AND f.devise_cod='EUR'  AND f.CLIENT_COD=SubStr(c.id,1,5) AND f.marque_cod=1) remdat_id,
           '9999' classe_cod,
           '9999' classeval_cod,
           0 seuil_qtmt,
           (SELECT REMBASEP_COD FROM &erp.ctr_remcod WHERE remcod_cod='RLA') rembase_cod,
           NULL combadv_id,
           NULL promo_cod,
           NULL refe_cod,
           NULL refe_ind,
           0 enreg_id,
           'Toutes les valeurs' classeval_lib,
           'P' remise_typ,
           remfac/100 remise_tx,
           NULL remise_mt,
           'N' sanscomm_typ,
           NULL fin_dat,
           NULL poidsvrt

  FROM uv_cmarque_client c
  WHERE remfac IS NOT NULL  )   ;


 UPDATE se_ctr_remise tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remise f WHERE f.remdat_id=tf.remdat_id AND f.classe_cod=tf.classe_cod AND f.CLASSEVAL_COD=tf.CLASSEVAL_COD AND f.SEUIL_QTMT=tf.SEUIL_QTMT),0) ;
 UPDATE se_ctr_remise tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;


 CALL uvse_synchroverserp('remdat_id@classe_cod@classeval_cod@seuil_qtmt','CTR_REMISE','O') ;

 commit;




DELETE FROM se_ctr_remise;

INSERT INTO se_ctr_remise
  (SELECT  DISTINCT
           (select remdat_id FROM &erp.ctr_remdat f
           WHERE f.remcod_cod='ARTA' AND f.DEBUT_DAT=r.datdeb2  AND f.devise_cod='EUR'  AND f.CLIENT_COD=r.client AND f.marque_cod=1) remdat_id,
           'REF' classe_cod,
           article classeval_cod,
           0 seuil_qtmt,
           (SELECT REMBASEP_COD FROM &erp.ctr_remcod WHERE remcod_cod='ARTA') rembase_cod,
           NULL combadv_id,
           NULL promo_cod,
           NULL refe_cod,
           NULL refe_ind,
           0 enreg_id,
           '.' classeval_lib,
           'P' remise_typ,
           taux2/100 remise_tx,
           NULL remise_mt,
           'N' sanscomm_typ,
           NULL fin_dat,
           NULL poidsvrt

  FROM  (
    SELECT r.*,To_Date('31/12/1967','dd/mm/yyyy')+db.datdeb2 datdeb2,To_Date('31/12/1967','dd/mm/yyyy')+df.datfin2 datfin2,t.taux2
    FROM uv_cremartp r,uv_cremartp_datdeb2 db,uv_cremartp_datfin2 df, uv_cremartp_taux2 t
    WHERE db.id=r.id
      AND df.id=db.id AND df.rangmulti2=db.rangmulti2
      AND df.id=t.id AND df.rangmulti2=t.rangmulti2
      AND r.cclasse IS null
  ) r )   ;


 UPDATE se_ctr_remise tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remise f WHERE f.remdat_id=tf.remdat_id AND f.classe_cod=tf.classe_cod AND f.CLASSEVAL_COD=tf.CLASSEVAL_COD AND f.SEUIL_QTMT=tf.SEUIL_QTMT),0) ;
 UPDATE se_ctr_remise tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;


 CALL uvse_synchroverserp('remdat_id@classe_cod@classeval_cod@seuil_qtmt','CTR_REMISE','O') ;

 commit;






DELETE FROM se_ctr_remise;

INSERT INTO se_ctr_remise
  (SELECT  DISTINCT
           (select remdat_id FROM &erp.ctr_remdat f
           WHERE f.remcod_cod='LIGA' AND f.DEBUT_DAT=r.datdeb2  AND f.devise_cod='EUR'  AND f.CLIENT_COD=r.client AND f.marque_cod=1) remdat_id,
           'LIG' classe_cod,
           article classeval_cod,
           0 seuil_qtmt,
           (SELECT REMBASEP_COD FROM &erp.ctr_remcod WHERE remcod_cod='LIGA') rembase_cod,
           NULL combadv_id,
           NULL promo_cod,
           NULL refe_cod,
           NULL refe_ind,
           0 enreg_id,
           '.' classeval_lib,
           'P' remise_typ,
           taux2/100 remise_tx,
           NULL remise_mt,
           'N' sanscomm_typ,
           NULL fin_dat,
           NULL poidsvrt

  FROM  (
    SELECT r.*,To_Date('31/12/1967','dd/mm/yyyy')+db.datdeb2 datdeb2,To_Date('31/12/1967','dd/mm/yyyy')+df.datfin2 datfin2,t.taux2
    FROM uv_cremartp r,uv_cremartp_datdeb2 db,uv_cremartp_datfin2 df, uv_cremartp_taux2 t
    WHERE db.id=r.id
      AND df.id=db.id AND df.rangmulti2=db.rangmulti2
      AND df.id=t.id AND df.rangmulti2=t.rangmulti2
      AND r.cclasse IS NOT null
  ) r )   ;


 UPDATE se_ctr_remise tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remise f WHERE f.remdat_id=tf.remdat_id AND f.classe_cod=tf.classe_cod AND f.CLASSEVAL_COD=tf.CLASSEVAL_COD AND f.SEUIL_QTMT=tf.SEUIL_QTMT),0) ;
 UPDATE se_ctr_remise tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;


 CALL uvse_synchroverserp('remdat_id@classe_cod@classeval_cod@seuil_qtmt','CTR_REMISE','O') ;

 commit;






DELETE FROM se_ctr_remise;

INSERT INTO se_ctr_remise
  (SELECT  DISTINCT
           (select remdat_id FROM &erp.ctr_remdat f
           WHERE f.remcod_cod='RPA' AND f.DEBUT_DAT='01/01/2015'  AND f.devise_cod='EUR'  AND f.CLIENT_COD=r.client AND f.marque_cod=1) remdat_id,
           '9999' classe_cod,
           '9999' classeval_cod,
           seuil seuil_qtmt,
           (SELECT REMBASEP_COD FROM &erp.ctr_remcod WHERE remcod_cod='RPA') rembase_cod,
           NULL combadv_id,
           NULL promo_cod,
           NULL refe_cod,
           NULL refe_ind,
           0 enreg_id,
           'Toutes les valeurs' classeval_lib,
           'P' remise_typ,
           taux/100 remise_tx,
           NULL remise_mt,
           'N' sanscomm_typ,
           NULL fin_dat,
           NULL poidsvrt

  FROM  (
    SELECT r.*,s.seuil,t.taux
    FROM uv_cremqp r,uv_cremqp_seuil s, uv_cremqp_taux t
    WHERE s.id=r.id
      AND t.id=s.id AND t.rangmulti2=s.rangmulti2

  ) r )   ;


 UPDATE se_ctr_remise tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remise f WHERE f.remdat_id=tf.remdat_id AND f.classe_cod=tf.classe_cod AND f.CLASSEVAL_COD=tf.CLASSEVAL_COD AND f.SEUIL_QTMT=tf.SEUIL_QTMT),0) ;
 UPDATE se_ctr_remise tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;


 CALL uvse_synchroverserp('remdat_id@classe_cod@classeval_cod@seuil_qtmt','CTR_REMISE','O') ;

 commit;





DELETE FROM se_ctr_remise;

INSERT INTO se_ctr_remise
  (SELECT  DISTINCT
           (select remdat_id FROM &erp.ctr_remdat f
           WHERE f.remcod_cod='NATA' AND f.DEBUT_DAT=r.datdeb  AND f.devise_cod='EUR'  AND f.CLIENT_COD=r.client AND f.marque_cod=1) remdat_id,
           'NAT' classe_cod,
           CLASSE classeval_cod,
           0 seuil_qtmt,
           (SELECT REMBASEP_COD FROM &erp.ctr_remcod WHERE remcod_cod='NATA') rembase_cod,
           NULL combadv_id,
           NULL promo_cod,
           NULL refe_cod,
           NULL refe_ind,
           0 enreg_id,
           '.' classeval_lib,
           'P' remise_typ,
           Nvl(REMEX,remsup)/100 remise_tx,
           NULL remise_mt,
           'N' sanscomm_typ,
           NULL fin_dat,
           NULL poidsvrt

  FROM  (
      SELECT r.*,rcc.cclasse,rc.classe,To_Date('31/12/1967','dd/mm/yyyy')+rdd.datdeb datdeb,To_Date('31/12/1967','dd/mm/yyyy')+rdf.datfin datfin,rex.remex,rsu.remsup
      FROM uv_cremsup r,uv_cremsup_cclasse rcc ,uv_cremsup_classe rc ,
          uv_cremsup_datdeb rdd,uv_cremsup_datfin rdf ,
          uv_cremsup_remex rex,uv_cremsup_remsup rsu
      WHERE r.id=rcc.id
        AND rc.id=rcc.id AND rc.RANGMULTI2=rcc.RANGMULTI2
        AND rdd.id=rc.id AND rdd.RANGMULTI2=rc.RANGMULTI2
        AND rdf.id=rdd.id AND rdf.RANGMULTI2=rdd.RANGMULTI2 AND rdf.RANGMULTI3=rdd.RANGMULTI3
        AND rex.id(+)=rdd.id AND rex.RANGMULTI2(+)=rdd.RANGMULTI2 AND rex.RANGMULTI3(+)=rdd.RANGMULTI3
        AND rsu.id(+)=rdd.id AND rsu.RANGMULTI2(+)=rdd.RANGMULTI2 AND rsu.RANGMULTI3(+)=rdd.RANGMULTI3


  ) r )   ;


 UPDATE se_ctr_remise tf SET enreg_id=Nvl((select enreg_id FROM &erp.ctr_remise f WHERE f.remdat_id=tf.remdat_id AND f.classe_cod=tf.classe_cod AND f.CLASSEVAL_COD=tf.CLASSEVAL_COD AND f.SEUIL_QTMT=tf.SEUIL_QTMT),0) ;
 UPDATE se_ctr_remise tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;


 CALL uvse_synchroverserp('remdat_id@classe_cod@classeval_cod@seuil_qtmt','CTR_REMISE','O') ;

 commit;
